import pymysql
import pandas as pd
from datetime import datetime
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
from sqlalchemy import create_engine
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')

# 上一次更新时间
from datetime import datetime


def update_device_vision(current_time):
    # 定义早上8点和晚上8点的时间
    morning_time = current_time.replace(hour=7, minute=0, second=0, microsecond=0)
    evening_time = current_time.replace(hour=19, minute=0, second=0, microsecond=0)
    yesterday_evening_time = evening_time - pd.DateOffset(days=1)
    # 检查当前时间是在早上8点和晚上8点之间，还是在晚上8点之后
    if current_time >= evening_time:
    # 如果当前时间在晚上8点之后，那么前一个时间点是晚上8点
        previous_time_point = evening_time
    elif current_time >= morning_time:
    # 如果当前时间在早上8点和晚上8点之间，那么前一个时间点是早上8点
        previous_time_point = morning_time
    elif current_time < morning_time:
    # 如果当前时间在早上8点之前，那么前一个时间点是昨天晚上8点
        previous_time_point = yesterday_evening_time

    #持续时间
    duration = current_time - previous_time_point
    duration_s = duration.total_seconds()
    # print(duration_s)
    # 编写SQL查询语句
    #previous_time_point_str = previous_time_point.strftime('%Y-%m-%d %H:%M:%S')
    sql1 = 'SELECT * FROM iotdemo WHERE receive_time >= %s'
    # 使用Pandas的read_sql函数执行SQL语句并获取数据
    df_iot = pd.read_sql(sql1,con=conn1,params=(previous_time_point,))
    #一号车间只包含1-252号的机器
    df_iot = df_iot[df_iot['dev'] > 252]
    #去掉df_iot中grp为6，dev为144的数据
    df_iot = df_iot[~((df_iot['grp'] == 6) & (df_iot['dev'] == 144))]
    #去掉df_iot中dev为169,170,171且num>20000的数据
    df_iot = df_iot[~(((df_iot['dev'] == 169) | (df_iot['dev'] == 170) | (df_iot['dev'] == 171)) & (df_iot['num'] > 20000))]



    #获取最近一个信号
    df_iot_last_signal = df_iot.groupby('dev').last().reset_index()
    df_iot_last_signal['dev_status'] = df_iot_last_signal['type'].apply(lambda x: 1 if x == '2' else 0)
    #df_iot_last_signal 对于每个dev若receive_time与current_time差距大于20分钟则dev_status为4
    df_iot_last_signal.loc[(current_time - df_iot_last_signal['receive_time']).dt.total_seconds() > 120, 'dev_status'] = 0
    df_iot_last_signal.loc[(current_time - df_iot_last_signal['receive_time']).dt.total_seconds() > 2400, 'dev_status'] = 4
    df_iot_status = df_iot_last_signal[['dev', 'dev_status']]
    #获取最近一个非0信号
    df_iot_last_non_zero_signal = df_iot[df_iot['type'] != '0'].groupby('dev').last().reset_index()
    #若receive_time与current_time差距大于30分钟则dev_status为4
    df_iot_last_non_zero_signal['dev_status_2'] = 0
    df_iot_last_non_zero_signal.loc[(current_time - df_iot_last_non_zero_signal['receive_time']).dt.total_seconds() > 1200, 'dev_status_2'] = 5
    df_iot_status_2 = df_iot_last_non_zero_signal[['dev', 'dev_status_2']]
    df_iot_status = pd.merge(df_iot_status, df_iot_status_2, on='dev', how='left')
    # 当dev_status_2为5或者 空 时 dev_status=5 其余时候不变
    df_iot_status.loc[df_iot_status['dev_status_2'].isnull(), 'dev_status'] = 5
    df_iot_status.loc[df_iot_status['dev_status_2'] == 5, 'dev_status'] = 5
    #去掉dev_status_2列
    df_iot_status = df_iot_status.drop(columns=['dev_status_2'])




   # print(df_iot_status)
    df = df_iot_status





#---------获取最后一次预警持续时间----------#
    df_iot_fix = pd.merge(df, df_iot, on='dev', how='left')

    df_iot_WARN = df_iot_fix[df_iot_fix['dev_status'] == 1][['dev','type','receive_time']]
    #将duration改成last_warn_time
    dev_list = df_iot_WARN['dev'].drop_duplicates().tolist()
    dev_groups = df_iot_WARN.groupby('dev')
    dev_arrays = {dev: group['type'].tolist() for dev, group in dev_groups}

    def count_consecutive_warns(lst):
        count = 0
        for item in reversed(lst):
            if item == '2':
                count += 1
            else:
                break
        return count

    last_warn_time = []
    for dev in dev_list:
        consecutive_warns = count_consecutive_warns(dev_arrays[dev])
        last_warn_time.append([dev, consecutive_warns * 0.084])
      #  print('consecutive_warns',consecutive_warns)
    df_last_warn_time = pd.DataFrame(last_warn_time, columns=['dev', 'last_warn_time'])

    # print(df_last_warn_time)
    df = pd.merge(df, df_last_warn_time, on='dev', how='left')
    # 如果最后一次预警持续时间超过30分钟则算为离线，即对于df last_warn_time大于30则dev_status=4
    df.loc[df['last_warn_time'] > 30, 'dev_status'] = 5






# --------------获取预警次数------------#
    df_warn_times = df_iot.sort_values(by=['dev', 'receive_time'])

    # 使用shift()获取前一条记录的type
    df_warn_times['prev_type'] = df_warn_times.groupby('dev')['type'].shift(1)
    # 筛选出type为'2'且前一条记录的type不为'2'的行
    filtered_df = df_warn_times[(df_warn_times['type'] == '2') & (df_warn_times['prev_type'] == '1')]
    # 按照dev分组，并计算符合条件的记录数量
    df_warn_times = filtered_df.groupby('dev').size().reset_index(name='warn_num')[['dev','warn_num']]
    #print(df_warn_times)
    df = pd.merge(df, df_warn_times, on='dev', how='left')

# -----------获取袜子数量信号----------------#
    df_iot_first_sock = df_iot[df_iot['type'] == '1'].groupby('dev').first().reset_index()[['dev', 'num']]
    df_iot_last_sock = df_iot[df_iot['type'] == '1'].groupby('dev').last().reset_index()[['dev', 'num']]
    df_iot_first_sock = df_iot_first_sock.rename(columns={'num': 'num_first'})
    df_iot_last_sock = df_iot_last_sock.rename(columns={'num': 'num_last'})
    # 去掉num_first和num_last两列
    df_iot_sock = pd.merge(df_iot_last_sock, df_iot_first_sock, on='dev')
    df_iot_sock['production'] = df_iot_sock['num_last'] - df_iot_sock['num_first']
    df_iot_sock = df_iot_sock.drop(columns=['num_first', 'num_last'])

    if df_iot_sock.empty:
        print('没有生产数据')
    else:
        df = pd.merge(df, df_iot_sock, on='dev', how='left')


# --------------平均时长--------------------------#

    df_sock_time = df_iot[df_iot['type'] == '1']
    # print(df_sock_time)

    df_sock_time['time_diff'] = (
        df_sock_time.groupby('dev')['receive_time']
        .transform(lambda x: x.diff().dt.total_seconds())
    )

    # 过滤异常值
    valid_mask = (df_sock_time['time_diff'] > 50) & (df_sock_time['time_diff'] < 350)
    avg_times = (
        df_sock_time[valid_mask]
        .groupby('dev', as_index=False)
        .agg(avg_time=('time_diff', lambda x: x.mean().round(1)))
    )

    # 安全合并
    df = df.merge(avg_times, on='dev', how='left').fillna({'avg_time': 0.0})

#-----------获取每个dev的总预警数据------------#
    df_iot_warn_num = df_iot[df_iot['type']=='2'][['dev','receive_time']]
    df_iot_warn_num['hour_minute'] = df_iot_warn_num['receive_time'].dt.strftime('%H:%M')
    df_iot_time = df_iot_warn_num.groupby(['dev','hour_minute']).first().reset_index()

    df_iot_time = df_iot_time[['dev','hour_minute']].groupby('dev').size().reset_index(name='warn_time')
    df_iot_time['warn_time'] = df_iot_time['warn_time']*1.00
    # print(df_iot_time)

    if df_iot_warn_num.empty:
        print('没有预警数据')
        df['work_time'] = duration_s / 3600.0
        df['warn_time'] = 0.0
    else:
        df['work_time'] = df['production'] * df['avg_time'] / 3600.0
        #print(df)
    df['work_time'] = df['work_time'].fillna(duration_s / 3600)
    #print(df[df['dev']==52][['dev', 'work_time', 'warn_time']])
    df['work_percent'] = df['work_time'] * 3600 / duration_s






    #df的dev改为device_id
    df = df.rename(columns={'dev': 'device_id'})


    #整合袜机排产
    sql2 = 'SELECT device_id,goods_id,group_id,goods_style,batch_id,status,operate_time,goods_id_old,goods_style_old,stuff_group FROM mm_new_device'
    df_device = pd.read_sql(sql2, con=conn2)


    #df右join df_ry
    df = pd.merge(df, df_device, on='device_id', how='right')

    df.to_csv('df.csv')
    #若df的status为2、3、5，则保持原值，若为0，1，4则替换为dev_status的值
    df['status'] = df.apply(lambda row: row['status'] if row['status'] == 3 else row['dev_status'], axis=1)
    df['status'] = df['status'].fillna(4)
    #dev>264的status设为4
    df.loc[df['device_id'] < 265, 'status'] = 4



    # 更新数据库mm_new_device表格中的device_id<253的部分
    #去掉dev_status
    df = df.drop(columns=['dev_status'])




    # # -----------整合iot状态表---------------#
    # df_iot_state = pd.read_sql('select * from iot_state', con=conn1)
    # # 将df_iot_state的dev_id列名改成dev
    # df = pd.merge(df, df_iot_state, on='dev', how='left')
    # # 若df的status为3，则保持原值，若为0，1，4则替换为state的值
    # df['status'] = df.apply(lambda row: row['status'] if row['status'] == 3 else row['state'], axis=1)
    # # 去掉state
    # df = df.drop(columns=['state'])


# -----------写入数据库----------------#
    #清空 conn2连接的数据中的'mm_new_device_vision'的数据
    sql2 = 'DELETE FROM ryerp.mm_new_device_vision_2'
    cursor = connect.cursor()
    cursor.execute(sql2)
    connect.commit()
    print(df.columns)
    df.to_sql('mm_new_device_vision_2', con=conn2, if_exists='append', index=False)
    print('写入数据库成功')

# 同时更新mm_new_device
    with connect.cursor() as cursor:
        # SQL 更新语句，添加了WHERE子句来限定device_id > 252
        sql = """
        UPDATE mm_new_device AS target
        JOIN mm_new_device_vision_2 AS source
        ON target.device_id = source.device_id
        SET target.status = source.status
        WHERE target.device_id > 252;
        """
        # 执行SQL语句
        cursor.execute(sql)
        # 提交事务
        connect.commit()
        print("更新成功")


# 获取当前日期和时间
while True:
    current_time = datetime.now()
    # 每隔1分钟更新一次
    try:
        update_device_vision(current_time)
    except Exception as e:
        print(f"An error occurred: {e}")
    print(datetime.now())
    time.sleep(10)



# 查看获取的数据





